Multiple Database Connection using Spring Data JPA
It is very common to have a monolithic application that connects and perform operations on multiple databases. Most organisations adopt this approach to enhance security by isolating different pieces of their data in different database systems such that in event of any of the database servers being compromised, the compromised data will not necessary be of any use to the hacker. For example, a banking application, governments ID databases, patient management systems etc.
In this post, we will develop a simple patient management system using Spring Boot (spring-data-jpa), where the patient information is stored in a MySQL database and the medical record stored in a PostgreSQL database. The developed application should interact with both databases in real time and perform various database operations.
Typically, Spring boot provides lots of auto configuration, but in a multi database driven application, we would need to add additional configuration in order to establish multiple database connections.
Maven Dependencies
In addition to related spring data dependencies, we need to include MySQL and PostgreSQL database connector dependencies.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
Application Properties
This is where we configure different database connection settings. Please update these settings to suit your needs.
# ===============================
# MYSQL DATA SOURCE
# ===============================
app.mysql.datasource.url= jdbc:mysql://localhost:3306/pms
app.mysql.datasource.username = root
app.mysql.datasource.password = password
# ===============================
# MYSQL HIKARI CONNECTION POOL
# ===============================
app.mysql.datasource.hikari.connection-timeout=10000
app.mysql.datasource.hikari.maximum-pool-size=10
app.mysql.datasource.hikari.minimum-idle= 1
app.mysql.datasource.hikari.pool-name= mysql-HikariPool
# ===============================
# PGSQL DATA SOURCE
# ===============================
app.pgsql.datasource.url= jdbc:postgresql://localhost:5432/computingfacts
app.pgsql.datasource.username = postgres
app.pgsql.datasource.password = password
# ===============================
# PGSQL HIKARI CONNECTION POOL
# ===============================
app.pgsql.datasource.connection-timeout=10000
app.pgsql.datasource.maximum-pool-size=10
app.pgsql.datasource.minimum-idle= 1
app.pgsql.datasource.pool-name= pgsql-HikariPool
# ===============================
# JPA / HIBERNATE
# ===============================
spring.jpa.database=default
spring.jpa.hibernate.ddl-auto = update
spring.jpa.show-sql = true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
Database Schema
To create the two tables that will be used in this application. we run these SQL scripts.
MySQL
CREATE TABLE `medical_record` (
`idmedical_record` bigint(20) NOT NULL AUTO_INCREMENT,
`patient_id` varchar(50) NOT NULL,
`case_report` longtext,
`doctor_id` bigint(20) NOT NULL,
`creation_date` datetime DEFAULT NULL,
PRIMARY KEY (`idmedical_record`),
UNIQUE KEY `idmedical_record_UNIQUE` (`idmedical_record`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
PostgreSQL
-- CREATE TABLE public.patient
CREATE TABLE public.patient
(
patient_id bigint NOT NULL DEFAULT nextval('patient_patient_id_seq'::regclass),
patient_no character varying COLLATE pg_catalog."default" NOT NULL,
patient_name character varying COLLATE pg_catalog."default" NOT NULL,
patient_dob date,
patient_address character varying COLLATE pg_catalog."default",
patient_email character varying COLLATE pg_catalog."default",
patient_phone_number numeric,
gp_surgery bigint,
creation_date date,
ni_no character varying COLLATE pg_catalog."default",
CONSTRAINT patient_pkey PRIMARY KEY (patient_id)
)
TABLESPACE pg_default;
ALTER TABLE public.patient
OWNER to postgres;
Application Configuration
The main application data configuration will comprise of Data source, Transaction management, spring data JPA repository and Entity manager factory bean
definition.
Step 1 - DataSource Configuration
We create a dataSource in order to establish a connection to the database using the configured individual database drivers.
@Primary
@Bean(name = "pgsqlDataSourceProperties")
@ConfigurationProperties("app.pgsql.datasource")
public DataSourceProperties pgsqlDataSourceProperties() {
return new DataSourceProperties();
}
@Primary
@Bean(name = "pgsqlDataSource")
@ConfigurationProperties("app.pgsql.datasource")
public DataSource pgsqlDataSource(@Qualifier("pgsqlDataSourceProperties") DataSourceProperties properties) {
return (HikariDataSource) properties.initializeDataSourceBuilder()
.type(HikariDataSource.class).build();
}
@Bean(name = "mysqlDataSourceProperties")
@ConfigurationProperties("app.mysql.datasource")
public DataSourceProperties mysqlDataSourceProperties() {
return new DataSourceProperties();
}
@Bean(name = "mysqlDataSource")
@ConfigurationProperties("app.mysql.datasource.hikari")
public DataSource mysqlDataSource(@Qualifier("mysqlDataSourceProperties") DataSourceProperties mysqlDataSourceProperties) {
return (HikariDataSource) mysqlDataSourceProperties.initializeDataSourceBuilder()
.type(HikariDataSource.class).build();
}
Because we have created two different @Bean
definition of dataSource, we need to mark one of them as the @Primary
dataSource otherwise, more than one dataSource of the same type will be detected by Spring IoC and the application will fail to start.
Step 2 – Entity Manager Factory Bean definition
We need an EntityManagerFactory to create an EntityManager that will manage the Entities in our application. To let Spring container manager our EntityManagerFactory, we need to setup a LocalContainerEntityManagerFactoryBean
using the EntityManagerFactoryBuilder
class which has provision for us to reference the required dataSource
and also the location (packages) of the entities
to manage.
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean pgsqlEntityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("pgsqlDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages(PACKAGES_TO_SCAN)
.build();
}
@Bean
public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("mysqlDataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages(PACKAGES_TO_SCAN)
.build();
}
Step 3 - Transaction Management configuration
Transaction management implies how we handle the persistence of our entities or database objects (perform validation checks, to commit the operation or not to), what happens if something goes wrong (rollback) and clean ups after the database operation. The aim of Transaction management is to ensure the consistency and validity of our data.
Spring Transaction provides an abstraction over the different transaction APIs, via PlatformTransactionManager
interface and for this example, we are using the JpaTransactionManager
an implementation of the PlatformTransactionManager
for a single JPA EntityManagerFactory
.
To create a transaction manager, we will need a reference of individual entity manager factory bean and finally, add the @EnableTransactionManagement
which is responsible for registering the necessary Spring components that powers annotation-driven transaction management capability.
@Bean
@Primary
public PlatformTransactionManager pgsqlTransactionManager(@Qualifier("pgsqlEntityManagerFactory") EntityManagerFactory pgsqlEntityManagerFactory) {
return new JpaTransactionManager(pgsqlEntityManagerFactory);
}
@Bean
public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEntityManagerFactory") EntityManagerFactory mysqlEntityManagerFactory) {
return new JpaTransactionManager(mysqlEntityManagerFactory);
}
Step 4 - JPA Repositories Configuration
We reference the appropriate entityManagerFactory, transactionManager and the repositories to be used.
Patient repository
@EnableJpaRepositories(
entityManagerFactoryRef = "pgsqlEntityManagerFactory",
transactionManagerRef = "pgsqlTransactionManager",
basePackages = "com.computingfacts.mds.repositories.patient"
)
Record repository
@EnableJpaRepositories(entityManagerFactoryRef = "mysqlEntityManagerFactory",
transactionManagerRef = "mysqlTransactionManager",
basePackages = "com.computingfacts.mds.repositories.record"
)
Repository Query Methods
Let us add some service methods to test and ensure that our application configuration can successfully connect to both databases (MySQL & PostgreSQL) and also perform some database operation.
Spring data Repository
@Repository
public interface PatientRepository extends JpaRepository<Patient, Long> {
Optional<Patient> findByPatientNo(String patientNo);
}
@Repository
public interface MedicalRecordRepository extends JpaRepository<MedicalRecord, Long> {
List<MedicalRecord> findByPatientNo(String patientNo);
}
Service methods Implementation
@Service("patientRecordService")
class PatientRecordServiceImpl implements PatientRecordService {
private final MedicalRecordRepository medicalRecordRepository;
private final PatientRepository patientRepository;
public PatientRecordServiceImpl(MedicalRecordRepository medicalRecordRepository, PatientRepository patientRepository) {
this.medicalRecordRepository = medicalRecordRepository;
this.patientRepository = patientRepository;
}
@Transactional("pgsqlTransactionManager")
@Modifying
@Override
public Patient addPatient(PatientForm patientForm) {
return patientRepository.findByPatientNo(patientForm.getPatientNo())
.orElseGet(() -> patientRepository.save(patientForm.toPatient()));
}
@Transactional("mysqlTransactionManager")
@Modifying
@Override
public MedicalRecord addMedicalRecord(MedicalRecordForm medicalRecordForm) {
return medicalRecordRepository.save(medicalRecordForm.toMedicalRecord());
}
@Transactional(value = "mysqlTransactionManager", readOnly = true)
@Override
public List<MedicalRecord> findMedicalRecordsByPatientNumber(String patientNo) {
return medicalRecordRepository.findByPatientNo(patientNo);
}
}
Application Testing –
Now let's test the code to ensure that 1. we using the configured data sources. 2. unit test against an in-memory database (H2) and 3. Integration test on both datbases (PostgreSQL & MySQL).
Configuration
@SpringBootTest
class MultiDatasourceApplicationTests {
@Qualifier("mysqlDataSource")
@Autowired
private DataSource mysqlDataSource;
@Qualifier("pgsqlDataSource")
@Autowired
private DataSource pgsqlDataSource;
@Test
void contextLoads() {
assertThat(mysqlDataSource).isNotNull();
assertThat(pgsqlDataSource).isNotNull();
assertThat(mysqlDataSource.toString()).isEqualTo("HikariDataSource (mysql-HikariPool)");
assertThat(pgsqlDataSource.toString()).isEqualTo("HikariDataSource (pgsql-HikariPool)");
}
}
Unit Test
@DataJpaTest
public class PatientRecordServiceImplTest {
@Autowired
private TestEntityManager testEntityManager;
@Autowired
private MedicalRecordRepository medicalRecordRepository;
@Autowired
private PatientRepository patientRepository;
@Test
void injectedComponentsAreNotNull() {
assertThat(testEntityManager).isNotNull();
assertThat(medicalRecordRepository).isNotNull();
assertThat(patientRepository).isNotNull();
}
/**
* Test of addPatient method, of class PatientRecordServiceImpl.
*/
@Test
public void testAddPatient() {
Patient patient = constructPatient("Boris Johnson", LocalDate.of(1950, Month.MARCH, 26));
Patient createdPatient = testEntityManager.persist(patient);
assertThat(createdPatient).isNotNull();
assertThat(createdPatient).hasFieldOrPropertyWithValue("patientName", patient.getPatientName());
assertNotNull(patientRepository.findById(createdPatient.getPatientId()));
}
/**
* Test of addMedicalRecord method, of class PatientRecordServiceImpl.
*/
@Test
public void testAddMedicalRecord() {
Patient patient = constructPatient("Boris Johnson", LocalDate.of(1950, Month.MARCH, 26));
Patient createdPatient = testEntityManager.persist(patient);
MedicalRecord record = constructMedicalRecord(createdPatient);
MedicalRecord createdPatientMedicalRecord = testEntityManager.persist(record);
assertThat(createdPatientMedicalRecord).isNotNull();
assertNotNull(medicalRecordRepository.findById(createdPatientMedicalRecord.getIdmedicalRecord()));
List<MedicalRecord> records = medicalRecordRepository.findByPatientNo(createdPatient.getPatientNo());
assertThat(records).hasSizeGreaterThan(0);
}
}
Integration Test
@SpringBootTest
public class PatientRecordServiceImplIT {
@Autowired
private PatientRecordService patientRecordService;
/**
* Test of addPatient method, of class PatientRecordServiceImpl.
*/
@Test
public void testAddPatient() {
PatientForm patientForm = constructPatientForm("Joe Biden", LocalDate.of(1930, Month.FEBRUARY, 27));
Patient createdPatient = patientRecordService.addPatient(patientForm);
assertThat(createdPatient).isNotNull();
assertThat(createdPatient).hasFieldOrPropertyWithValue("patientName", patientForm.getPatientName());
}
/**
* Test of addMedicalRecord method, of class PatientRecordServiceImpl.
*/
@Test
public void testAddMedicalRecord() {
PatientForm patientForm = constructPatientForm("Mike Jordan", LocalDate.of(1970, Month.SEPTEMBER, 11));
Patient createdPatient = patientRecordService.addPatient(patientForm);
MedicalRecordForm record = constructMedicalRecord(createdPatient);
MedicalRecord createdPatientMedicalRecord = patientRecordService.addMedicalRecord(record);
assertThat(createdPatientMedicalRecord).isNotNull();
List<MedicalRecord> records = patientRecordService.findMedicalRecordsByPatientNumber(createdPatient.getPatientNo());
assertThat(records).hasSizeGreaterThan(0);
}
}
As usual the source code for this example application is available on GitHub. Thanks for reading and please do leave a comment.